{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calling PL/SQL\n",
    "\n",
    "Documentation reference link: [Executing PL/SQL](https://python-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html)\n",
    "\n",
    "PL/SQL is a 'stored' procedural language that is stored and run inside the database itself. PL/SQL lets you capture business logic for reuse across all your applications.  You can call stored procedures and functions easily from python-oracledb."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import oracledb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "un = \"pythondemo\"\n",
    "pw = \"welcome\"\n",
    "cs = \"localhost/orclpdb1\"\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PL/SQL Procedures\n",
    "\n",
    "This shows the PL/SQL procedure `MYPROC` used in this demo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "   \n",
    "    cursor.execute(\"select dbms_metadata.get_ddl('PROCEDURE', 'MYPROC') from dual\")\n",
    "    ddl, = cursor.fetchone()\n",
    "    print(ddl.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use `callproc()` to call the procedure.  Bind variables are passed by position:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    myinvar  = 22\n",
    "    myoutvar = cursor.var(int)    # allocate a 'variable' of integer type to hold the OUT bind parameter\n",
    "\n",
    "    cursor.callproc('myproc', [myinvar, myoutvar])\n",
    "    print(myoutvar.getvalue())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also call PL/SQL procedures via an 'anonymous' PL/SQL block.  This can be useful if you want to use named bind placeholders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    myinvar  = 33\n",
    "    myoutvar = cursor.var(int)\n",
    "\n",
    "    cursor.execute(' begin myproc(:v1, :v2); end;', {\"v1\": myinvar, \"v2\": myoutvar})\n",
    "    print(myoutvar.getvalue())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PL/SQL Functions\n",
    "\n",
    "This shows the PL/SQL function `MYFUNC` used in this demo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    \n",
    "    cursor.execute(\"select dbms_metadata.get_ddl('FUNCTION', 'MYFUNC') from dual\")\n",
    "    ddl, = cursor.fetchone()\n",
    "    print(ddl.read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use `callfunc()` to call the function. Bind variables are passed by position.  The second argument to `callfunc()` is the type of the PL/SQL function return value.  Here it is an integer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    data = \"abc\"\n",
    "    id = 3\n",
    "    res = cursor.callfunc('myfunc', int, (data, id))\n",
    "    print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to calling PL/SQL procedures, you can also invoke PL/SQL procedures via an anonymous block, and optionally used named bind placeholders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    data  = \"def\"\n",
    "    id = 4\n",
    "    ret = cursor.var(int)\n",
    "\n",
    "    cursor.execute(' begin :ret := myfunc(:data, :id); end;', {\"ret\": ret, \"data\": data, \"id\": id})\n",
    "    print(ret.getvalue())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## REF CURSORS\n",
    "\n",
    "REF CURSORS let result sets be returned to python-oracledb, commonly from PL/SQL.\n",
    "\n",
    "Here is the PL/SQL procedure used in this example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    cursor.execute(\"\"\"select text from all_source \n",
    "                      where name = 'MYREFCURSORPROC' and type = 'PROCEDURE' \n",
    "                      order by line\"\"\")\n",
    "    rows = cursor.fetchall()\n",
    "    for r, in rows:\n",
    "        print(r, end=\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `callproc()` as shown before to call the PL/SQL procedure. The `ref_cursor` variable needs to be defined as a cursor so it can hold the returned REF CURSOR.  This second cursor is then simply iterated over exactly like a cursor for simple SELECT would be:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    ref_cursor = connection.cursor()\n",
    "\n",
    "    cursor.callproc(\"myrefcursorproc\", (2, 6, ref_cursor))\n",
    "\n",
    "    print(\"Rows between 2 and 6:\")\n",
    "    for row in ref_cursor:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implicit Cursors\n",
    "\n",
    "Instead of binding a cursor to get a REF CURSOR, the `dbms_sql.return_result()` procedure can alternatively return a result set back which is fetched in python-oracledb using `getimplicitresults()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "\n",
    "    cursor.execute(\"\"\"\n",
    "            declare\n",
    "                c1 sys_refcursor;\n",
    "                c2 sys_refcursor;\n",
    "            begin\n",
    "                open c1 for\n",
    "                select * from ParentTable;\n",
    "                dbms_sql.return_result(c1);\n",
    "\n",
    "                open c2 for\n",
    "                select * from ChildTable;\n",
    "                dbms_sql.return_result(c2);\n",
    "            end;\"\"\")\n",
    "\n",
    "    for resultSet in cursor.getimplicitresults():\n",
    "        print(\"Result Set:\")\n",
    "        for row in resultSet:\n",
    "           print(row)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
